# class Person(object):
#     name='xx'
#     contry='yy'
#     age=18
#
# # create table person(name varchar(20),age tinyint,contry varchar(64))
# # person 对应数据表的名字
# #name 属性 对应  字段name
# #类的一个对象 数据表中的一条数据

from sqlalchemy import create_engine,Column,Integer,String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
#需要 declarative_base 根据创建好的引擎 创建 ORM基类
# 数据库的配置变量
HOSTNAME = '127.0.0.1'
PORT     = '3306'
DATABASE = 'tornado1'
USERNAME = 'root'
PASSWORD = '123456'
#DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)
DB_URI = 'mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8'.format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

# 创建数据库引擎
engine = create_engine(DB_URI)

#根据engine 创建基类

Base = declarative_base(engine)

#session = sessionmaker(engine) #这样得出来是一个类 我们需要的是一个对象 所以在后面再加一个()
session = sessionmaker(engine)()  #session要在base下面

# #创建连接
# # with engine.connect() as con:
# #     rs = con.execute('SELECT * from stu')
# #     print (rs.fetchone())

#创建ORM模型

class Person(Base):
    __tablename__='person'
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(50),nullable=False,unique=True)
    age = Column(Integer)
    country = Column(String(50))
    def __str__(self):
        return "<Person(name:%s;age:%s;country:%s)>" % (self.name,self.age,self.country)

#将 Person 映射到数据库中
Base.metadata.create_all()

def add_data():
    p1 = Person(name='jiangjie',age=18,country='china')
    p2 = Person(name='91wangfan',age=19,country='wuhan')
    session.add_all([p1,p2])
    session.commit()
def select_data():
    # all_p = session.query(Person).all()
    # for p in all_p:
    #     print(p)
    # all_p = session.query(Person).filter_by(name='jiangjie').all()
    # for p in all_p:
    #     print(p)
    # all_p = session.query(Person).filter(Person.name=="91wangfan").all()
    # for p in all_p:
    #     print(p)
    person1 = session.query(Person).first()
    print(person1)
if __name__ == "__main__":
    # add_data()
    select_data()